package com.tiancheng.trade.settlement.util;

import com.tiancheng.trade.settlement.bo.ExportBO;
import com.tiancheng.trade.settlement.vo.settlement.api.CellMergeVO;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.WorkbookUtil;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.util.Assert;
import org.springframework.util.CollectionUtils;

import jakarta.servlet.http.HttpServletRequest;
import jakarta.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ExcelUtil {

    public static final String SUFFRIX = ".xlsx";

    public static Workbook buildWorkbook(String sheetName, String[] title, List<Map<String, Object>> dataList) throws IOException {
        return buildWorkbook((workbook) -> {
            String safeSheetName = WorkbookUtil.createSafeSheetName(sheetName);
            Sheet sheet = workbook.createSheet(safeSheetName);

            int firstRowIndex = 0;
            int cellIndex = 0;
            if (title != null) {
                Row firstRow = sheet.createRow(firstRowIndex);
                for (String str : title) {
                    firstRow.createCell(cellIndex).setCellValue(str);
                    cellIndex++;
                }
            }
            if (dataList == null) {
                return;
            }
            int rowIndex = firstRowIndex;
            for (Map<String, Object> dataRow : dataList) {
                rowIndex++;
                Row row = sheet.createRow(rowIndex);
                cellIndex = 0;
                for (Map.Entry<String, Object> dataCell : dataRow.entrySet()) {
                    Object cell = dataCell.getValue();
                    String v = cell == null ? "" : cell.toString();
                    row.createCell(cellIndex).setCellValue(v);
                    cellIndex++;
                }
            }
            try {
                autoSizeColumn(sheet, cellIndex);
            } catch (Exception e) {
                e.printStackTrace();
            }
        });
    }

    /**
     * @return
     * @Author Liu Pinghui
     * @Description 设置自适应宽度
     * @Date 2019/9/12 10:49
     * @Param
     **/
    private static void autoSizeColumn(Sheet sheet, Integer maxColumn) throws Exception {
        //列宽自适应，只对英文和数字有效
        for (int i = 0; i <= maxColumn; i++) {
            sheet.autoSizeColumn(i);
        }
        //获取当前列的宽度，然后对比本列的长度，取最大值
        for (int columnNum = 0; columnNum <= maxColumn; columnNum++) {
            int columnWidth = sheet.getColumnWidth(columnNum) / 256;
            for (int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++) {
                Row currentRow;
                //当前行未被使用过
                if (sheet.getRow(rowNum) == null) {
                    currentRow = sheet.createRow(rowNum);
                } else {
                    currentRow = sheet.getRow(rowNum);
                }

                if (currentRow.getCell(columnNum) != null) {
                    Cell currentCell = currentRow.getCell(columnNum);
                    int length = currentCell.toString().getBytes("GBK").length;
                    if (columnWidth < length + 1) {
                        columnWidth = length + 1;
                    }
                }
            }
            Integer width = columnWidth * 256;
            if (width < 25 * 256) {
                sheet.setColumnWidth(columnNum, 25 * 256 - 1);
            }else if (width < 255 * 256) {
                sheet.setColumnWidth(columnNum, width);
            } else {
                sheet.setColumnWidth(columnNum, 255 * 256 - 1);
            }
        }
    }

    public static List<CellMergeVO> getOneRowCellMergeData(int[] colEnd) {
        List<CellMergeVO> list = new ArrayList<>();
        for (int i = 0; i < colEnd.length; i++) {
            CellMergeVO vo = new CellMergeVO();
            vo.setStartRow(0);
            if(i>0){
                vo.setStartCol(colEnd[i-1]);
            }else{
                vo.setStartCol(0);
            }
            vo.setEndRow(0);
            vo.setEndCol(colEnd[i]-1);
            list.add(vo);
        }
        return list;
    }

    public static Workbook buildExcelWorkBook(List<ExportBO> exportBOS) throws IllegalAccessException {
        Assert.isTrue(!CollectionUtils.isEmpty(exportBOS), "参数不能为空！");
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFCellStyle headerStyle = (XSSFCellStyle) workbook.createCellStyle();
        headerStyle.setAlignment(HorizontalAlignment.CENTER);
        headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        headerStyle.setFillForegroundColor(new XSSFColor(new java.awt.Color(105,105,105), new DefaultIndexedColorMap()));
        XSSFFont font = workbook.createFont();
        font.setColor(new XSSFColor(new java.awt.Color(255,255,255)));
        font.setFontHeight(13);
        headerStyle.setFont(font);
        headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        headerStyle.setBorderBottom(BorderStyle.THIN); //下边框
        headerStyle.setBorderLeft(BorderStyle.THIN);//左边框
        headerStyle.setBorderTop(BorderStyle.THIN);//上边框
        headerStyle.setBorderRight(BorderStyle.THIN);//右边框
        for (ExportBO exportBO : exportBOS) {
            String safeSheetName = WorkbookUtil.createSafeSheetName(exportBO.getSheetName());
            Sheet sheet = workbook.createSheet(safeSheetName);
            XSSFCellStyle style = (XSSFCellStyle) workbook.createCellStyle();
            style.setAlignment(HorizontalAlignment.CENTER);
            style.setVerticalAlignment(VerticalAlignment.CENTER);

            int firstRowIndex = 0;
            //需要合并的标题
            //生成表头合并格式
            List<CellMergeVO> cellMergeList=new ArrayList<>();
            if (exportBO.getMergeTitle() != null) {
                Row firstRow = sheet.createRow(firstRowIndex);
                firstRow.setHeight((short) (30*20));
                firstRowIndex = 1;
                List<Integer> colBeginIdx = new ArrayList<>();
                for (Integer key : exportBO.getMergeTitle().keySet()) {
                    if (exportBO.getMergeTitle().get(key) != null) {
                        Cell cell = firstRow.createCell(key);
                        cell.setCellValue(exportBO.getMergeTitle().get(key));
                        cell.setCellStyle(headerStyle);
                    }
                    colBeginIdx.add(key);
                }
                for (int i = 0; i < colBeginIdx.size(); i++) {
                    CellMergeVO vo = new CellMergeVO();
                    vo.setStartRow(0);
                    vo.setStartCol(colBeginIdx.get(i));
                    if(i==colBeginIdx.size()-1){
                        vo.setEndCol(exportBO.getAllTitle().length-1);
                    }else{
                        vo.setEndCol(colBeginIdx.get(i+1)-1);
                    }
                    cellMergeList.add(vo);
                }
            }
            int cellIndex = 0;
            //数据标题
            if (exportBO.getAllTitle() != null) {
                Row secondRow = sheet.createRow(firstRowIndex);
                secondRow.setHeight((short) (30*20));
                for (String str : exportBO.getAllTitle()) {
                    Cell cell = secondRow.createCell(cellIndex);
                    cell.setCellValue(str);
                    cell.setCellStyle(headerStyle);
                    cellIndex++;
                }
            }
            if (CollectionUtils.isEmpty(exportBO.getDataRow())) {
                return workbook;
            }
            //需要合并的列
            if (!CollectionUtils.isEmpty(cellMergeList)) {
                for (CellMergeVO vo : cellMergeList) {
                    CellRangeAddress region = new CellRangeAddress(vo.getStartRow(), vo.getEndRow(), vo.getStartCol(), vo.getEndCol());
                    sheet.addMergedRegion(region);
                }
            }
            int rowIndex = firstRowIndex;
            for (Object t : exportBO.getDataRow()) {
                rowIndex++;
                Row row = sheet.createRow(rowIndex);
                cellIndex = 0;
                Field[] fields = t.getClass().getDeclaredFields();
                for (Field field : fields) {
                    field.setAccessible(true);
                    String keyName = field.getName();
                    Object cell = field.get(t);
                    String v = cell == null ? "" : cell.toString();
                    row.createCell(cellIndex).setCellValue(v);
                    cellIndex++;
                }
            }
            try {
                autoSizeColumn(sheet, cellIndex);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return workbook;
    }

    /**
     * 导出Excel并支持标题合并
     *
     * @param sheetName
     * @param title1        需要合并的标题
     * @param title2        数据列表标题
     * @param dataList
     * @param cellMergeList 合并规则
     * @return
     * @throws IOException
     */
    public static Workbook buildWorkbook2(String sheetName, HashMap<Integer, String> title1, String[] title2, List<Map<String, Object>> dataList, List<CellMergeVO> cellMergeList) throws IOException {
        return buildWorkbook((workbook) -> {
            String safeSheetName = WorkbookUtil.createSafeSheetName(sheetName);
            Sheet sheet = workbook.createSheet(safeSheetName);
            XSSFCellStyle style = (XSSFCellStyle) workbook.createCellStyle();
            style.setAlignment(HorizontalAlignment.CENTER);
            style.setVerticalAlignment(VerticalAlignment.CENTER);
            int firstRowIndex = 0;
            //需要合并的标题
            if (title1 != null) {
                Row firstRow = sheet.createRow(firstRowIndex);
                for (Integer key : title1.keySet()) {
                    if (title1.get(key) != null) {
                        Cell cell = firstRow.createCell(key);
                        cell.setCellValue(title1.get(key));
                        cell.setCellStyle(style);
                    }
                }
            }
            firstRowIndex = 1;
            int cellIndex = 0;
            //数据标题
            if (title2 != null) {

                Row firstRow = sheet.createRow(firstRowIndex);
                for (String str : title2) {
                    Cell cell = firstRow.createCell(cellIndex);
                    cell.setCellValue(str);
                    cell.setCellStyle(style);
                    cellIndex++;
                }
            }
            if (dataList == null) {
                return;
            }
            //需要合并的列
            if (cellMergeList != null && !cellMergeList.isEmpty()) {
                for (CellMergeVO vo : cellMergeList) {
                    CellRangeAddress region = new CellRangeAddress(vo.getStartRow(), vo.getEndRow(), vo.getStartCol(), vo.getEndCol());
                    sheet.addMergedRegion(region);
                }
            }
            int rowIndex = firstRowIndex;
            for (Map<String, Object> dataRow : dataList) {
                rowIndex++;
                Row row = sheet.createRow(rowIndex);
                cellIndex = 0;
                for (Map.Entry<String, Object> dataCell : dataRow.entrySet()) {
                    Object cell = dataCell.getValue();
                    String v = cell == null ? "" : cell.toString();
                    row.createCell(cellIndex).setCellValue(v);
                    cellIndex++;
                }
            }

            try {
                autoSizeColumn(sheet, cellIndex);
            } catch (Exception e) {
                e.printStackTrace();
            }
        });
    }

    public static Workbook buildWorkbook(SheetHandler handler) throws IOException {
        Assert.notNull(handler, "handler不能为空");
        Workbook workbook = new XSSFWorkbook();
        handler.process(workbook);
        return workbook;
    }

    public static void exportExcle(HttpServletRequest request, HttpServletResponse response, Workbook workbook, String fileName) throws IOException {
        //解决不同浏览器中文乱码问题
        String userAgent = request.getHeaders("User-Agent").nextElement().toLowerCase();
        if (userAgent.contains("msie") || userAgent.contains("like gecko")) {
            fileName = URLEncoder.encode(fileName, "UTF-8");
        } else {
            fileName = new String(fileName.getBytes("UTF-8"), "iso-8859-1");
        }
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
        response.setContentType("application/force-download");
        workbook.write(response.getOutputStream());
    }

    public static class ExcelWorkbook{

    }

}
